package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.tool.CDateTimeTool;
import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.DataRangeUtils;
import com.fzrskj.cloud.free.user.bean.UserDiyBean;
import com.fzrskj.cloud.free.user.bean.UserDiyViewBean;
import com.winiis.component.common.utils.db.DbUtils;
import com.winiis.component.common.utils.other.MapOperateUtils;
import ms.core.consts.SysErr;
import ms.core.db.DbUtilsDrv;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import ms.core.tool.StrTool;
import org.apache.commons.collections.MapUtils;

import java.util.List;
import java.util.Map;
import java.util.ArrayList;

public class UserDiyDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserDiyDao() {
        db = new DbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        if (db != null) {
            db.closeConnection();
        }
        db = null;
        super.dispose();
    }

    //new record to database by map
    public int newUserDiy(Map<String, Object> values) {
        //代理商数据隔离
        values.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.insertRecord("user_diy", values);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        this.setErrCode(err);
        return ret;
    }

    public Integer getLastId() {
        return DbUtils.lastId(db);
    }

    //to delete table record by id
    public int delUserDiy(int id) {
        String sql = "delete from user_diy where id=?";

        Object[] params = {id};
        //代理商数据隔离
        /*sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());*/

        int ret = db.executeSql(sql, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        this.setErrCode(err);
        return ret;
    }

    //to delete table record by cdt
    public int delUserDiy(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.deleteRecord("user_diy", cdt);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //to modify table record by values and condition
    public int modUserDiy(Map<String, Object> values, Map<String, Object> cdt) {
        //代理商数据隔离
        //cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.modifyRecord("user_diy", values, cdt);
        String err = db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    public int modTransferUserDiy(Map<String, Object> values, Map<String, Object> cdt) {
        int ret = db.modifyRecord("user_diy", values, cdt);
        String err = db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    //to query user_diy item for detail
    public UserDiyBean queryUserDiy(int id) {
        String sql = "select * from user_diy where id=?";

        Object[] params = {id};
        //代理商数据隔离
        /*sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());*/


        UserDiyBean ret = db.queryForBean(sql, UserDiyBean.class, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    public UserDiyBean qryUserDiy(int id) {
        String sql = "select * from user_diy where id=?";
        Object[] params = {id};

        UserDiyBean ret = db.queryForBean(sql, UserDiyBean.class, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //to query UserDiy record count
    public int queryUserDiy(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.queryRecordCount("user_diy", cdt);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    //to query UserDiyBean for record list with condition,if page or pagesize less than 0, means without page
    public List<UserDiyBean> queryUserDiy(Map<String, Object> cdt, String orderby, int page, int pagesize) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        List<UserDiyBean> rets = db.queryForBeans("user_diy", "*", orderby, UserDiyBean.class, cdt, page, pagesize);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        this.setErrCode(err);
        return rets;
    }

    /**
     * 用户获取业务列表总数.
     *
     * @param cdt
     * @return
     */
    public Integer userQuery(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuilder sbSql = new StringBuilder("select count(1) ");
        List<Object> params = new ArrayList<>();
        this.userQueryWhere(sbSql, params, cdt);

        Integer total = DbUtils.getCountForMixSql(this.db, sbSql, params);
        String err = this.db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        return total;
    }

    /**
     * 用户获取业务列表.
     *
     * @param cdt
     * @param orderBy
     * @param page
     * @param pageSize
     * @return
     */
    public List<UserDiyViewBean> userQuery(Map<String, Object> cdt, String orderBy, Integer page, Integer pageSize) {
        //代理商数据隔离
        cdt.put("diy.agentId", CloudContextUtils.getAgentId());

        StringBuilder sbSql = new StringBuilder("select diy.* ");
        sbSql.append(", ifnull((select cat.categoryName from sys_product_diycat cat where cat.categoryCode=diy.subClass limit 1), diy.subClass) as subClassName ");
        sbSql.append(", (select productParam from sys_product product where product.productCode=diy.productCode ").
                append(" and product.agentId=0 and product.productClass='diy') as productParam ");
        List<Object> params = new ArrayList<>();
        this.userQueryWhere(sbSql, params, cdt);

        List<UserDiyViewBean> viewBeans = DbUtils.queryForBeans(this.db, sbSql, params, orderBy, page, pageSize, UserDiyViewBean.class);
        String err = this.db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        return viewBeans;
    }

    public List<UserDiyViewBean> queryExpiryDiy(String startTime,String endTime, String subClass){
        StringBuilder sql = new StringBuilder("select * from user_diy where  ");
        sql.append(" endTime >= ? and endTime <= ?");
        List<Object> params = new ArrayList<>();
        params.add(startTime);
        params.add(endTime);
        if (!StrTool.isBlankStr(subClass)) {
            sql.append(" and  subClass = ? ");
            params.add(subClass);
        }
        String orderBy = " endTime desc ";
        int page = 1;
        int pageSize = 10000;
        List<UserDiyViewBean> viewBeans = DbUtils.queryForBeans(this.db, sql, params, orderBy, page, pageSize, UserDiyViewBean.class);
        String err = this.db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        return viewBeans;
    }


    private void userQueryWhere(StringBuilder sbSql, List<Object> params, Map<String, Object> cdt) {
        sbSql.append(" from user_diy diy where isdel=0 ");
        if (cdt.containsKey("id")) {
            sbSql.append(" and id=? ");
            params.add(cdt.get("id"));
        }
        if (cdt.containsKey("userId")) {
            sbSql.append(" and diy.userId=? ");
            params.add(cdt.get("userId"));
        }
        if (cdt.containsKey("agentId")) {
            sbSql.append(" and diy.agentId=? ");
            params.add(cdt.get("agentId"));
        }
        if (cdt.containsKey("subClass")) {
            sbSql.append(" and diy.subClass=? ");
            params.add(cdt.get("subClass"));
        }
        if (cdt.containsKey("keyword")) {
            sbSql.append(" and (diy.productName like ? or diy.openInfo like ?) ");
            String keyword = MapOperateUtils.getStr(cdt, "keyword");
            params.add(keyword);
            params.add(keyword);
        }
        if (cdt.containsKey("endDateType")) {
            sbSql.append(" and endTime < ? ");
            params.add(CDateTimeTool.getEndDateByType(MapOperateUtils.getInt(cdt, "endDateType")));
        }
    }


    /**
     * 管理员获取业务列表总数.
     *
     * @param cdt
     * @return
     */
    public Integer adminQuery(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuilder sbSql = new StringBuilder("select count(1) ");
        List<Object> params = new ArrayList<>();
        this.adminQueryWhere(sbSql, params, cdt);

        Integer total = DbUtils.getCountForMixSql(this.db, sbSql, params);
        String err = this.db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        return total;
    }

    /**
     * 管理员获取业务列表.
     *
     * @param cdt
     * @param orderBy
     * @param page
     * @param pageSize
     * @return
     */
    public List<UserDiyViewBean> adminQuery(Map<String, Object> cdt, String orderBy, Integer page, Integer pageSize) {
        //代理商数据隔离
        cdt.put("diy.agentId", CloudContextUtils.getAgentId());

        StringBuilder sbSql = new StringBuilder("select diy.* ");
        sbSql.append(", ifnull((select cat.categoryName from sys_product_diycat cat where cat.categoryCode=diy.subClass limit 1), diy.subClass) as subClassName ");
        sbSql.append(", (select userName from user_account account where account.userId=diy.userId limit 1) as userName ");
        sbSql.append(", (select paramValue from dic_param dpc where dpc.agentId=diy.agentId and dpc.paramName='webname' limit 1) as platName ");
        List<Object> params = new ArrayList<>();
        this.adminQueryWhere(sbSql, params, cdt);

        List<UserDiyViewBean> viewBeans = DbUtils.queryForBeans(this.db, sbSql, params, orderBy, page, pageSize, UserDiyViewBean.class);
        String err = this.db.getErrCode();
        this.setErrCode(err);
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        return viewBeans;
    }

    private void adminQueryWhere(StringBuilder sbSql, List<Object> params, Map<String, Object> cdt) {
        sbSql.append(" from user_diy diy where isdel=0 ");
        if (cdt.containsKey("id")) {
            sbSql.append(" and diy.id=? ");
            params.add(cdt.get("id"));
        }
        if (cdt.containsKey("subClass")) {
            sbSql.append(" and diy.subClass=? ");
            params.add(cdt.get("subClass"));
        }
        if (cdt.containsKey("keyword")) {
            sbSql.append(" and (diy.productName like ? or diy.openInfo like ? or diy.userId in (select userId from user_account user where user.userName like ?)) ");
            String keyword = MapOperateUtils.getStr(cdt, "keyword");
            params.add(keyword);
            params.add(keyword);
            params.add(keyword);
        }
        if (cdt.containsKey("endDateType")) {
            sbSql.append(" and diy.endTime < ? ");
            params.add(CDateTimeTool.getEndDateByType(MapOperateUtils.getInt(cdt, "endDateType")));
        }
        if (cdt.containsKey("start")) {
            sbSql.append(" and DATE_FORMAT( diy.endTime, '%Y-%m-%d')>=DATE_FORMAT(?, '%Y-%m-%d') ");
            params.add(cdt.get("start"));
        }
        if (cdt.containsKey("end")) {
            sbSql.append(" and DATE_FORMAT( diy.endTime, '%Y-%m-%d')<=DATE_FORMAT(?, '%Y-%m-%d') ");
            params.add(cdt.get("end"));
        }
        if (cdt.containsKey("startFrom")) {
            sbSql.append(" and DATE_FORMAT(diy.startTime, '%Y-%m-%d')>=DATE_FORMAT(?, '%Y-%m-%d') ");
            params.add(cdt.get("startFrom"));
        }
        if (cdt.containsKey("startLimit")) {
            sbSql.append(" and DATE_FORMAT(diy.startTime, '%Y-%m-%d')<=DATE_FORMAT(?, '%Y-%m-%d') ");
            params.add(cdt.get("startLimit"));
        }
        //数据隔离
        DataRangeUtils.formatRangeSql(sbSql, " diy.userId ", params);

        Integer agentId = CloudContextUtils.getAgentId();
        if (cdt.containsKey("agentId")) {
            agentId = MapUtils.getInteger(cdt, "agentId");
        }
        AgentDataRangeUtils.formatDataRange(agentId, sbSql, "diy.agentId");
    }
}